package ro.gateway.aida.obj.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.*;

import ro.gateway.aida.db.DBPersistenceManager;
import ro.gateway.aida.db.PersistenceToken;
import ro.gateway.aida.obj.AIDASector;
import ro.gateway.aida.servlet.Constants;
import ro.gateway.aida.srv.IIDGenerator;
import ro.gateway.aida.stats.StatsHolder;
import ro.gateway.aida.stats.StatsItem;

/**
 * <p/>
 * Title: Romanian AIDA
 * </p>
 * <p/>
 * Description: :D application
 * </p>
 * <p/>
 * Copyright: Copyright (comparator) 2003
 * </p>
 * <p/>
 * Company: Romania Development Gateway
 * </p>
 *
 * @author Mihai Popoaei, mihai_popoaei@yahoo.com, smike@intellisource.ro
 * @version $Id: AIDASectorPersMan.java,v 1.2 2005/01/26 10:35:58 mihaipostelnicu Exp $
 */
public class AIDASectorPersMan extends DBPersistenceManager {

	private AIDASectorPersMan(PersistenceToken token) {
		super(token);
	}

	public static AIDASectorPersMan getManager(PersistenceToken token) {
		return new AIDASectorPersMan(token);
	}

	/**
	 * Returneaza toate sectoarele in limba specificata
	 *
	 * @param lang
	 * @return @throws
	 *         SQLException
	 */
	public AIDASector[] getAllByLang(String lang) throws SQLException {
		return _getAllByLangAndParent(lang, -2);
	}

	public StatsHolder getAllStatsByLang(String lang) {
		return _getAllStatsByLangAndParent(lang, -2);
	}

	public StatsHolder getAllByLangAndParent(String lang, int parent_id) {
		return _getAllStatsByLangAndParent(lang, parent_id);
	}

	/**
	 * Returneaza sectoarele primare in limba specificata
	 *
	 * @param lang
	 * @return @throws
	 *         SQLException
	 */
	public AIDASector[] getAllRootsByLang(String lang) throws SQLException {
		return _getAllByLangAndParent(lang, -1);
	}

	/**
	 * Returneaza subsectoarele pt. sectorul specificat in limba specificata
	 *
	 * @param lang
	 * @param parent_id
	 * @return @throws
	 *         SQLException
	 */
	public AIDASector[] getAllByLangForParent(String lang, int parent_id)
		  throws SQLException {
		return _getAllByLangAndParent(lang, parent_id);
	}

	/**
	 * Daca parent_id == -2 iau toate sectoarele <p/>Daca parent_id == -1 iou
	 * sectoarele fara parinte <p/>Daca parent_id >= 0 iau sectoarele cu
	 * parintele specificat
	 *
	 * @param lang
	 * @param parent_id
	 * @return @throws
	 *         SQLException
	 */
	private AIDASector[] _getAllByLangAndParent(String lang, int parent_id)
		  throws SQLException {
		Connection con = getConnection();
		ArrayList items = new ArrayList();
		AIDASector[] result = null;

		try {
			PreparedStatement ps = null;
			//               System.out.println( "parent_id:" + parent_id );
			if (parent_id == -2) {
				ps = con.prepareStatement(GET_ALL_BY_LANG);
				ps.setString(1, lang);
				ps.setString(2, lang);
			} else if (parent_id == -1) {
				ps = con.prepareStatement(GET_ALL_BY_LANG_AND_PARENT_NULL);
				ps.setString(1, lang);
				ps.setString(2, lang);
			} else {
				ps = con.prepareStatement(GET_ALL_BY_LANG_AND_PARENT);
				ps.setString(1, lang);
				ps.setString(2, lang);
				ps.setInt(3, parent_id);
			}
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				AIDASector item = new AIDASector();

				item.setRo_aida_code(rs.getInt(1));
				item.setDac5_code(rs.getString(2));
				if (rs.wasNull()) {
					item.setDac5_code(null);
				}
				item.setCrs_code(rs.getString(3));
				if (rs.wasNull()) {
					item.setCrs_code(null);
				}
				item.setParent_ro_aida_code(rs.getInt(4));
				if (rs.wasNull()) {
					item.setParent_ro_aida_code(-1);
				}
				String name = rs.getString(5);
				item.setTitle(lang, name);

				items.add(item);
			}
			rs.close();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}

		if (items.size() > 0) {
			result = new AIDASector[items.size()];
			items.toArray(result);
		}

		return result;
	}

	private StatsHolder _getAllStatsByLangAndParent(String lang, int parent_id) {
		Connection con = getConnection();
		StatsHolder sh = new StatsHolder("Sectors");

		try {
			PreparedStatement ps = null;
			if (parent_id == -2) {
				ps = con.prepareStatement(GET_ALL);

			} else if (parent_id == -1) {
				ps = con.prepareStatement(GET_ALL_BY_PARENT_NULL);

			} else {
				ps = con.prepareStatement(GET_ALL_BY_PARENT);

				ps.setInt(1, parent_id);
			}
			ResultSet rs = ps.executeQuery();
			int cid = -2;
			StatsItem citem = null;
			while (rs.next()) {
				if (cid != rs.getInt(1)) {
					cid = rs.getInt(1);
					citem = new StatsItem();
					citem.getName().setViewLang(lang);
					citem.setId(rs.getInt(1));
					sh.add(citem);
				}

				citem.getName().setString(rs.getString(3), rs.getString(2));
			}
			rs.close();
			ps.close();
		} catch (SQLException sqlEx) {
			return null;
		} finally {
			try {
				con.close();
			} catch (Exception ex) {
			}
		}

		return sh;
	}

	public void insert(AIDASector item) throws SQLException {
		HashMap titles = item.getTitle().getStrings();
		if (titles.size() < 1)
			return;
		Iterator it = titles.keySet().iterator();

		IIDGenerator idGenerators = db_token.getIdgenerators();
		if (item.getRo_aida_code() == -1) {
			item.setRo_aida_code((int) idGenerators
				  .nextVal(Constants.IDGEN_SECTORS));
		}
		Connection con = getConnection();

		try {
			PreparedStatement ps = con.prepareStatement(INSERT);
			while (it.hasNext()) {
				String lang = (String) it.next();
				ps.clearParameters();
				ps.setInt(1, item.getRo_aida_code());
				ps.setString(2, item.getDac5_code());
				ps.setString(3, item.getCrs_code());
				if (item.getParent_ro_aida_code() == -1) {
					ps.setNull(4, Types.INTEGER);
				} else {
					ps.setInt(4, item.getParent_ro_aida_code());
				}
				ps.setString(5, item.getTitle(lang));
				ps.setString(6, lang);
				ps.executeUpdate();
			}

			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}
	}

	public void delete(AIDASector item) throws SQLException {
		Connection con = getConnection();
		try {
			PreparedStatement ps = con.prepareStatement(DELETE_BY_RO_AIDA_CODE);
			ps.setInt(1, item.getRo_aida_code());
			ps.executeUpdate();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}

	}

	public void update(AIDASector item) throws SQLException {
		delete(item);
		insert(item);
	}

	public long getLastId() throws SQLException {

		long result = 0;
		Connection con = getConnection();

		try {
			PreparedStatement ps = con.prepareStatement(GET_LAST_ID);

			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				result = rs.getLong(1);
			}
			rs.close();

			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}

		return result;
	}

	public final AIDASector getById(int ro_aida_sector) throws SQLException {

		Connection con = getConnection();
		AIDASector result = null;

		try {
			PreparedStatement ps = con.prepareStatement(GET_BY_ID);
			ps.setInt(1, ro_aida_sector);

			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				if (result == null) {
					result = new AIDASector();
					result.setRo_aida_code(ro_aida_sector);
					result.setDac5_code(rs.getString(1));
					result.setCrs_code(rs.getString(2));
					result.setParent_ro_aida_code(rs.getInt(3));
					if (rs.wasNull()) {
						result.setParent_ro_aida_code(-1);
					}
				}

				String title = rs.getString(4);
				String lang = rs.getString(5);
				result.setTitle(lang, title);
			}
			rs.close();

			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}

		return result;
	}

	public Vector getSectorsByID(String list, String lang) {

		Vector ret = new Vector();
		StringTokenizer st = new StringTokenizer(list, ",");
		while (st.hasMoreTokens()) {
			try {
				ret.add(getById(Integer.parseInt(st.nextToken())));
			} catch (NumberFormatException ex) {
				ex.printStackTrace();
			} catch (SQLException ex) {
				ex.printStackTrace();
			}
		}

		return ret;
	}

	/**
	 * crs_code == -1 -> null in db
	 *
	 * @param dac5_code
	 * @param crs_code
	 * @return @throws
	 *         SQLException
	 */
	public final AIDASector getByHarmCodes(String dac5_code, String crs_code)
		  throws SQLException {

		Connection con = getConnection();
		AIDASector result = null;

		try {
			PreparedStatement ps = null;
			if (crs_code == null) {
				ps = con.prepareStatement(GET_BY_HARM_CODES_CRS_NULL);
				ps.setString(1, dac5_code);
			} else {
				ps = con.prepareStatement(GET_BY_HARM_CODES);
				ps.setString(1, dac5_code);
				ps.setString(2, crs_code);
			}

			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				if (result == null) {
					result = new AIDASector();
					result.setRo_aida_code(rs.getInt(1));
					result.setParent_ro_aida_code(rs.getInt(2));
					if (rs.wasNull()) {
						result.setParent_ro_aida_code(-1);
					}
					result.setDac5_code(dac5_code);
					result.setCrs_code(crs_code);
				}

				String lang = rs.getString(3);
				result.setTitle(lang, rs.getString(4));
			}
			rs.close();

			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}

		return result;
	}

	public AIDASector[] findSectors(String what, String language)
		  throws SQLException {
		if (what == null)
			return null;

		Connection con = getConnection();
		AIDASector[] result = null;
		ArrayList items = new ArrayList();

		try {
			PreparedStatement ps = con.prepareStatement(FIND_SECTORS);
			ps.setString(1, "%" + what + "%");
			ps.setString(2, language);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				AIDASector item = new AIDASector();

				item.setRo_aida_code(rs.getInt(1));
				item.setDac5_code(rs.getString(2));
				item.setCrs_code(rs.getString(3));
				item.setTitle(language, rs.getString(4));

				int parent_ro_aida_code = rs.getInt(5);
				if (!rs.wasNull()) {
					AIDASector parent = new AIDASector();
					parent.setRo_aida_code(parent_ro_aida_code);
					parent.setDac5_code(rs.getString(6));
					parent.setCrs_code(rs.getString(7));
					parent.setTitle(language, rs.getString(8));
					parent.setParent_ro_aida_code(rs.getInt(9));
					item.setParent_ro_aida_code(parent_ro_aida_code);
					item.setParent(parent);
				} else {
					item.setParent_ro_aida_code(-1);
				}

				items.add(item);
			}
			rs.close();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}

		if (items.size() > 0) {
			result = new AIDASector[items.size()];
			items.toArray(result);
		}

		return result;
	}

	public boolean canDelete(AIDASector sector) throws SQLException {
		Connection con = getConnection();
		int deps = 0;
		try {
			PreparedStatement ps = con.prepareStatement(COUNT_CHILDREN);
			ps.setInt(1, sector.getRo_aida_code());
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				deps += rs.getInt(1);
			}
			rs.close();
			ps.close();

			ps = con.prepareStatement(COUNT_DEPS);
			ps.setInt(1, sector.getRo_aida_code());
			rs = ps.executeQuery();
			if (rs.next()) {
				deps += rs.getInt(1);
			}
			rs.close();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}

		return deps == 0;
	}

	/**
	 * Returneaza o parcurgerea arborelui de sectoare intr-un flatlist
	 *
	 * @return
	 */
	public AIDASector[] getAllSectorsFlatList() {
		AIDASector result[] = null;
		return result;
	}



	public AIDASector[] getAllRoots() throws SQLException {
		AIDASector result[] = null;

		ArrayList items = new ArrayList();
		Connection con = getConnection();
		try {
			PreparedStatement ps = con.prepareStatement(GET_ALL_BY_PARENT_NULL);
			ResultSet rs = ps.executeQuery();
			int last_code = -1;
			AIDASector last_sector = null;
			while (rs.next()) {
				int code = rs.getInt(1);
				if (code != last_code) {
					// new sector
					if (last_sector != null) {
						items.add(last_sector);
					}
					last_sector = new AIDASector();
					last_sector.setRo_aida_code(code);
					last_code = code;
				}

				String name = rs.getString(2);
				String lang = rs.getString(3);
				last_sector.setTitle(lang, name);
			}
			if (last_sector != null) {
				items.add(last_sector);
			}
			rs.close();
			ps.close();
		} catch (SQLException sqlex) {
			throw sqlex;
		} finally {
			con.close();
		}

		if (items.size() > 0) {
			System.out.println("found " + items.size() + " root sectors") ;
			result = new AIDASector[items.size()];
			items.toArray(result);
		}

		return result;
	}

	public AIDASector[] getAllForParent(int ro_aida_code) throws SQLException {
		AIDASector result[] = null;

		ArrayList items = new ArrayList();
		Connection con = getConnection();
		try {
			PreparedStatement ps = con.prepareStatement(GET_ALL_BY_PARENT);
			ps.setInt(1, ro_aida_code);
			ResultSet rs = ps.executeQuery();
			int last_code = -1;
			AIDASector last_sector = null;
			while (rs.next()) {
				int code = rs.getInt(1);
				if (code != last_code) {
					// new sector
					if (last_sector != null) {
						items.add(last_sector);
					}
					last_sector = new AIDASector();
					last_sector.setRo_aida_code(code);
					last_code = code;
				}

				String name = rs.getString(2);
				String lang = rs.getString(3);
				last_sector.setTitle(lang, name);
			}
			if (last_sector != null) {
				items.add(last_sector);
			}
			rs.close();
			ps.close();
		} catch (SQLException sqlex) {
			throw sqlex;
		} finally {
			con.close();
		}

		if (items.size() > 0) {
			result = new AIDASector[items.size()];
			items.toArray(result);
		}

		return result;
	}

	//      SELECT s.ro_aida_code, s.dac5_code, s.crs_code, s.name,
	//      p.ro_aida_code, p.dac5_code, p.crs_code, p.name
	//      FROM ro_aida_sectors p RIGHT OUTER JOIN ro_aida_sectors s
	//      ON s.parent_ro_aida_code=p.ro_aida_code
	//      WHERE s.name like '%EMERG%';
	public static final String FIND_SECTORS = "SELECT s.ro_aida_code, s.dac5_code, s.crs_code, s.name, "
		  + "p.ro_aida_code, p.dac5_code, p.crs_code, p.name, p.parent_ro_aida_code "
		  + "FROM ro_aida_sectors p RIGHT OUTER JOIN ro_aida_sectors s "
		  + "ON s.parent_ro_aida_code=p.ro_aida_code "
		  + "WHERE s.name ilike ? AND s.lang = ? AND (s.lang=p.lang OR p.lang IS NULL)";

	public static final String GET_ALL_BY_LANG = "SELECT ro_aida_code, dac5_code, crs_code, parent_ro_aida_code,  lpd_get_sector_name(ro_aida_code,?) "
		  + "FROM ro_aida_sectors " + "WHERE lang=? " + "ORDER BY name ASC ";

	public static final String GET_ALL = "SELECT ro_aida_code, name, lang "
		  + "FROM ro_aida_sectors ORDER BY ro_aida_code,lang";

	public static final String GET_ALL_BY_PARENT = "SELECT ro_aida_code , name, lang "
		  + "FROM ro_aida_sectors WHERE parent_ro_aida_code = ? "
		  + "ORDER BY ro_aida_code,lang";

	public static final String GET_ALL_BY_PARENT_NULL = "SELECT ro_aida_code,name,lang "
		  + "FROM ro_aida_sectors WHERE parent_ro_aida_code IS NULL "
		  + "ORDER BY ro_aida_code,lang";

	public static final String GET_ALL_BY_LANG_AND_PARENT = "SELECT ro_aida_code, dac5_code, crs_code, parent_ro_aida_code, lpd_get_sector_name(ro_aida_code,?) "
		  + "FROM ro_aida_sectors "
		  + "WHERE lang = ? AND parent_ro_aida_code = ? "
		  + "ORDER BY name ASC ";

	public static final String GET_ALL_BY_LANG_AND_PARENT_NULL = "SELECT ro_aida_code, dac5_code, crs_code, parent_ro_aida_code, lpd_get_sector_name(ro_aida_code,?)"
		  + "FROM ro_aida_sectors "
		  + "WHERE lang = ? AND parent_ro_aida_code IS NULL "
		  + "ORDER BY name ASC ";

	public static final String INSERT = "INSERT INTO ro_aida_sectors (ro_aida_code, dac5_code, crs_code, "
		  + "parent_ro_aida_code, name, lang) " + "VALUES (?, ?, ?, ?, ?, ?)";

	public static final String GET_LAST_ID = "SELECT max(ro_aida_code) FROM ro_aida_sectors";

	public static final String GET_BY_ID = "SELECT dac5_code, crs_code, parent_ro_aida_code, name, lang "
		  + "FROM ro_aida_sectors WHERE ro_aida_code=?";

	public static final String GET_BY_HARM_CODES = "SELECT ro_aida_code, parent_ro_aida_code, lang, name "
		  + "FROM ro_aida_sectors WHERE dac5_code=? AND crs_code=?";

	public static final String GET_BY_HARM_CODES_CRS_NULL = "SELECT ro_aida_code, parent_ro_aida_code, lang, name "
		  + "FROM ro_aida_sectors WHERE dac5_code=? AND crs_code IS NULL";

	public static final String DELETE_BY_RO_AIDA_CODE = "DELETE FROM ro_aida_sectors WHERE ro_aida_code = ?";

	public static final String COUNT_CHILDREN = "SELECT count(1) FROM ro_aida_sectors WHERE parent_ro_aida_code=?";

	public static final String COUNT_DEPS = "SELECT count(1) FROM sectors WHERE ro_aida_code=?";

}